﻿from Config.ImportConfig import *
from DataAccess.DBConnFactory import DBConnFactory

import xlrd
import traceback
from datetime import date

def import_daily_position(ref_date):

	str_ref_date = ref_date.strftime('%Y%m%d')
	
	FILE_SUFFIX = '.xls'
	positon_file_path = TRADE_FILE_DIR + POSITION_FILE_PREFIX + str_ref_date + FILE_SUFFIX
	print positon_file_path
	
	ws_handle = None
	try:
		wb_handle = xlrd.open_workbook(filename=positon_file_path)			
		ws_handle = wb_handle.sheet_by_index(0)
		
		row = 1
		while(ws_handle.nrows > row):
		
			ticker = ws_handle.cell_value(row, 0)
			amt = ws_handle.cell_value(row, 2)
			avg_c = ws_handle.cell_value(row, 3)
			portf_id = ws_handle.cell_value(row, 4)
			type = 'CASH' if (ticker=='9999999') else 'STOCK'
							
			conn = DBConnFactory().get_db_connection('PKEDB')
			conn.cursor().execute('''insert into position 
								(ref_date, portfolio_id, ticker, security_type, amount, avg_cost_price) 
								values (TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6)''', 
								(ref_date.isoformat(), portf_id, ticker, type, amt, avg_c))
			conn.commit()
			row += 1
			
		#while
		
	except IOError, e:
		print "error, open workbook or worksheet failed, ", unicode(e)
	except StandardError, e:
		print "error, ", unicode(e)
	#finally:
	

def import_hkd_portfolio_for_test():
	conn = DBConnFactory().get_db_connection('PKEDB')
	conn.cursor().executemany('''insert into position 
							(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
							values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)''', 
							[('2012-06-01','SC028','01262','STOCK',10000,2.4,'HKD'), 
							('2012-06-01','SC028','08102','STOCK',20000,2.80,'HKD'), 
							('2012-06-01','SC028','XUN2 Index','FUTURES',20000,7440,'USD'), 
							('2012-06-01','SC028','XS0464501329','PNOTE',100000,0.8054,'USD'), 
							('2012-06-01','SC028','9999999','CASH',100000,1,'USD')]) 
							#('2012-06-01','SC028','9999999','CASH',100000,1,'HKD')   #multiple cash account

	conn.cursor().execute('''insert into pnote_info values('XS0464501329','600070','USD','CNY')''')	
	conn.commit()
	
